package org.upfrost.idiom;

import java.util.HashSet;
import java.util.List;

/**
 * An implementation of the SQL92 idiom. This manages all the reserved keywords, as defined by the sql92 standard, 
 * and automatically quote them when used as identifiers.
 * 
 * @author Rodrigo Reyes
 *
 */
public class GenericSQL92 extends SQLIdiom
{
	private boolean m_forceQuoteColumnIdentifiers = false;

	static private String[] RESERVED_KEYWORDS = {
		"ABSOLUTE",  "ACTION",  "ADD",  "ALL",  "ALLOCATE",  "ALTER",  "AND",
		"ANY",  "ARE",  "AS",  "ASC", "ASSERTION",  "AT",  "AUTHORIZATION", 
		"AVG", "BEGIN",  "BETWEEN",  "BIT",  "BIT_LENGTH", "BOTH",  "BY",
		"CASCADE",  "CASCADED",  "CASE",  "CAST",  "CATALOG",  "CHAR",  "CHARACTER", 
		"CHAR_LENGTH", "CHARACTER_LENGTH", "CHECK",  "CLOSE",  "COALESCE",  "COLLATE",
		"COLLATION", "COLUMN",  "COMMIT",  "CONNECT",  "CONNECTION",  "CONSTRAINT", 
		"CONSTRAINTS",  "CONTINUE", "CONVERT",  "CORRESPONDING",  "COUNT",  "CREATE",
		"CROSS",  "CURRENT", "CURRENT_DATE", "CURRENT_TIME",  "CURRENT_TIMESTAMP", 
		"CURRENT_USER", "CURSOR", "DATE",  "DAY",  "DEALLOCATE",  "DEC",  "DECIMAL",  
		"DECLARE",  "DEFAULT",  "DEFERRABLE", "DEFERRED",  "DELETE",  "DESC",  
		"DESCRIBE",  "DESCRIPTOR",  "DIAGNOSTICS", "DISCONNECT",  "DISTINCT",  "DOMAIN",  
		"DOUBLE",  "DROP", "ELSE",  "END",  "END-EXEC", "ESCAPE",  "EXCEPT",  
		"EXCEPTION", "EXEC",  "EXECUTE",  "EXISTS", "EXTERNAL",  "EXTRACT", "FALSE",  
		"FETCH",  "FIRST",  "FLOAT",  "FOR",  "FOREIGN",  "FOUND",  "FROM",  "FULL", 
		"GET",  "GLOBAL",  "GO",  "GOTO",  "GRANT",  "GROUP", "HAVING",  "HOUR", 
		"IDENTITY",  "IMMEDIATE",  "IN",  "INDICATOR",  "INITIALLY",  "INNER",  
		"INPUT", "INSENSITIVE",  "INSERT",  "INT",  "INTEGER",  "INTERSECT",  
		"INTERVAL",  "INTO",  "IS", "ISOLATION", "JOIN", "KEY", "LANGUAGE",  
		"LAST",  "LEADING",  "LEFT",  "LEVEL",  "LIKE",  "LOCAL",  "LOWER", "MATCH",  
		"MAX",  "MIN",  "MINUTE",  "MODULE",  "MONTH", "NAMES",  "NATIONAL",  "NATURAL",  
		"NCHAR",  "NEXT",  "NO",  "NOT",  "NULL", "NULLIF",  "NUMERIC", "OCTET_LENGTH", 
		"OF",  "ON",  "ONLY",  "OPEN",  "OPTION",  "OR", "ORDER",  "OUTER", "OUTPUT",  
		"OVERLAPS", "PAD",  "PARTIAL",  "POSITION",  "PRECISION",  "PREPARE",  
		"PRESERVE",  "PRIMARY", "PRIOR",  "PRIVILEGES",  "PROCEDURE",  "PUBLIC", 
		"READ",  "REAL",  "REFERENCES",  "RELATIVE",  "RESTRICT",  "REVOKE",  "RIGHT", 
		"ROLLBACK",  "ROWS", "SCHEMA",  "SCROLL",  "SECOND",  "SECTION",  "SELECT",  
		"SESSION",  "SESSION_USER", "SET", "SIZE",  "SMALLINT",  "SOME",  "SPACE",  
		"SQL",  "SQLCODE",  "SQLERROR",  "SQLSTATE", "SUBSTRING",  "SUM",  
		"SYSTEM_USER", "TABLE",  "TEMPORARY",  "THEN",  "TIME",  "TIMESTAMP",  "TIMEZONE_HOUR", 
		"TIMEZONE_MINUTE", "TO",  "TRAILING",  "TRANSACTION",  "TRANSLATE",  "TRANSLATION",  "TRIM", 
		"TRUE", "UNION",  "UNIQUE",  "UNKNOWN",  "UPDATE",  "UPPER",  "USAGE", 
		"USER",  "USING", "VALUE",  "VALUES",  "VARCHAR",  "VARYING",  "VIEW",
		"WHEN",  "WHENEVER",  "WHERE",  "WITH",  "WORK",  "WRITE", "YEAR",
		"ZONE"
	};

	protected HashSet<String> s_keywords = new HashSet<String>();

	public GenericSQL92()
	{
		this(RESERVED_KEYWORDS);
	}

	protected GenericSQL92(String[] reservedKeywords)
	{
		for (String s: reservedKeywords)
			s_keywords.add(s);
	}
	
	@Override
	public String normalizeColumnIdentifier(String name) 
	{
		if (m_forceQuoteColumnIdentifiers)
			return addQuotesToIdentifier(name);

		if (s_keywords.contains(name.toUpperCase()))
			return addQuotesToIdentifier(name);

		return name;
	}

	protected String addQuotesToIdentifier(String name)
	{
		return "\"" + name + "\""; 
	}
	
	public boolean isForceQuoteColumnIdentifiers() {
		return m_forceQuoteColumnIdentifiers;
	}

	public void setForceQuoteColumnIdentifiers(boolean forceQuoteColumnIdentifiers) {
		m_forceQuoteColumnIdentifiers = forceQuoteColumnIdentifiers;
	}

	@Override
	public String createUpdateStatement(String table, List<String> columns,	List<String> whereClause) 
	{
		StringBuilder res = new StringBuilder();

		res.append("update " + normalizeColumnIdentifier(table));

		if (columns != null && columns.size()>0)
		{
			res.append(" set ");
			int index = 0;
			for (String str: columns)
			{
				if (index++>0)
					res.append(",");
				res.append(normalizeColumnIdentifier(str)+"=?");
			}
		}

		if (whereClause != null && whereClause.size()>0)
		{
			res.append(" where ");
	    	int index = 0;
	    	for (String key: whereClause)
	    	{
	    		if (index++>0)
	    			res.append(" AND ");
	    		res.append(normalizeColumnIdentifier(key)+"=?");
	    	}
		}
		
		return res.toString();
	}

	@Override
	public String createInsertStatement(String table, List<String> columns) 
	{
		StringBuilder res = new StringBuilder();
		
    	res.append("insert into " + normalizeColumnIdentifier(table) + " (");

    	int index = 0;
    	for (String col : columns)
    	{
    		if (index++>0)
    			res.append(",");
    		res.append(normalizeColumnIdentifier(col));
    	}

    	res.append(") values (");
    	for (int i=0; i<columns.size(); i++)
    	{
    		if (i>0)
    			res.append(",");
    		res.append("?");
    	}
    	res.append(")");

		return res.toString();
	}

	@Override
	public String createSelectStatement(String table, List<String> columns, List<String> whereClause, List<Order> orderClause, Limit limit) 
	{
		StringBuilder res = new StringBuilder();
		
		res.append("select ");

		if (columns != null && columns.size()>0)
		{
	    	int index = 0;
	    	for (String col : columns)
	    	{
	    		if (index++>0)
	    			res.append(",");
	    		res.append(normalizeColumnIdentifier(col));
	    	}
		}
		else
		{
			res.append("*");
		}
		
		res.append(" from " + normalizeColumnIdentifier(table));
		
		if (whereClause != null && whereClause.size()>0)
		{
			res.append(" where ");
	    	int index = 0;
	    	for (String key: whereClause)
	    	{
	    		if (index++>0)
	    			res.append(" and ");
	    		res.append(key+"=?");
	    	}
		}

		if (orderClause != null && orderClause.size()>0)
		{
			res.append(" order by ");
			
	    	int index = 0;
	    	for (Order o: orderClause)
	    	{
	    		if (index++>0)
	    			res.append(",");
	    		res.append(o.getColumn() + " " + (o.isAsc()?"ASC":"DESC"));
	    	}
		}

		// Don't use LIMIT
		if (limit != null)
		{
			throw new RuntimeException(GenericSQL92.class.getSimpleName()+ " does not handle LIMIT clauses");
		}
		
		return res.toString();
	}

	@Override
	public String createDeleteStatement(String table, List<String> whereClause, Limit limit)
	{
		StringBuilder res = new StringBuilder();
		
		res.append("delete");

		res.append(" from " + normalizeColumnIdentifier(table));
		
		if (whereClause != null && whereClause.size()>0)
		{
			res.append(" where ");
	    	int index = 0;
	    	for (String key: whereClause)
	    	{
	    		if (index++>0)
	    			res.append(" and ");
	    		res.append(key+"=?");
	    	}
		}

		// Don't use LIMIT
		if (limit != null)
		{
			throw new RuntimeException(GenericSQL92.class.getSimpleName()+ " does not handle LIMIT clauses");
		}
		
		return res.toString();
	}

	

}
